package com.hkts.reportform.controller;

import com.hkts.reportform.basedao.BaseDao;
import com.hkts.reportform.datastatics.dao.CustomSearchDao;
import com.hkts.reportform.datastatics.dao.ReportDao;
import com.hkts.reportform.datastatics.entity.ReportForm;
import com.hkts.reportform.utils.ErpProperties;
import com.hkts.reportform.utils.InventoryClassUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.sql.DataSource;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("/test")
public class TestController {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private ReportDao reportDaoImpl;
    @Autowired
    private NamedParameterJdbcTemplate temple;
    @Autowired
    private BaseDao baseDao;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    private DataSource dataSource;
    @Autowired
    private CustomSearchDao customSearchDao;
    //现存量查询
    @RequestMapping("/customStockSearch")
    public List<Map<String,Object>> customStockSearch(String beginDate,String endDate, String codes){
     return   customSearchDao.cusomeFindStock(beginDate,endDate,codes);
    }

    //生产sql测试
    @RequestMapping("/finishedProductsData")
    public List<Map<String,Object>> finishedProductsData(String beginDate,String endDate, String codes){
        return   customSearchDao.finishedProductsData(beginDate,endDate,codes);
    }
    @RequestMapping("/testSqlerver")
    public Map<String, Object> testSqlerver() {
        HashMap<String, Object> stringObjectHashMap = new HashMap<>();
        String sql = "SELECT * " +
                "FROM (" +
                "    SELECT " +
                "        *," +
                "        ROW_NUMBER() OVER(ORDER BY [Taskitemid] DESC) AS num" +
                "    FROM WorkTaskItem where CreateDateTime BETWEEN :date1 and :date2 " +
                ") AS t " +
                "WHERE t.num BETWEEN 11 AND 20";

        // List<Map<String,Object>> query = jdbcTemplate.queryForList(sql);
        Map<String, Object> map = new HashMap<>();
        map.put("date1", "2011-04-13 21:18:13");
        map.put("date2", "2012-11-02 19:28:34");
        List<Map<String, Object>> maps = temple.queryForList(sql, map);
        stringObjectHashMap.put("statu", maps);
        return stringObjectHashMap;
    }

    @RequestMapping("/testSqlerverDateType")
    public Map<String, Object> testSqlerverDateType() {
        Map<String, Object> stringObjectHashMap = new HashMap<>();
        String sql = "select * from WorkTaskItem where CreateDateTime BETWEEN '2011-04-13 21:18:13' and  '2012-11-02 19:28:34'";
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        stringObjectHashMap.put("result", maps);
        return stringObjectHashMap;
    }

    //测试存储过程  stored procedure
    @RequestMapping("/testStoredProcedure")
    public Map<String, Object> testStoredProcedure() throws Exception {
        Map<String, Object> stringObjectHashMap = new HashMap<>();
        Connection connection = dataSource.getConnection();
        String date1 = "2020-10-10";
        String date2 = "2020-10-10";
        String type = "0101\t0102\t0103";
        String storedProc = "{call hbyb_scbb_202008(?,?,?)}";
        try {
            // 建立数据库的连接
            CallableStatement cstmt = connection.prepareCall(storedProc);
            cstmt.setString(1, date1);
            cstmt.setString(2, date2);
            cstmt.setString(3, type);
            // 查询
            ResultSet rs = cstmt.executeQuery();
            while(rs.next()){
                System.out.println(rs.getString(1)+","+rs.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return stringObjectHashMap;
    }
    //测试存货类别
    @RequestMapping("/getInventoryClassS")
    public  List<Map<String,Object>> getInventoryClassS() throws Exception{

        return reportDaoImpl.selectInventory();
    }
   //测试封装存储过程
   @RequestMapping("/getList")
    public  List<ReportForm> getList(String date1,String date2,String[] classArry) throws Exception{

        return reportDaoImpl.getStoredProcedure(date1,date2, InventoryClassUtils.getInventoryClassStr(classArry));
    }
    //测试redis数据取出
    @RequestMapping("/getRedis")
    public  Map<String,Object> getRedis() throws Exception{
        Map<String,Object> map=new HashMap<>();
        map.put(ErpProperties.ASPHALT_CONSUME,redisTemplate.opsForList().range(ErpProperties.ASPHALT_CONSUME,0,-1));
        map.put(ErpProperties.ASPHALT_STOCK,redisTemplate.opsForList().range(ErpProperties.ASPHALT_STOCK,0,-1));
        return map;
    }
}
